package br.com.skytef.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpSession;

import br.com.skytef.entity.DetailProductEntity;
import br.com.skytef.jdbc.ConnectionFactory;
import br.com.skytef.utils.utils;

public class DetailProductDao {
	public Connection connection;
	
	public DetailProductDao(){
		this.connection = new ConnectionFactory().getConnection();
	}
	
	public DetailProductDao(HttpSession session){
		this.connection = utils.recoverSessionDBConnection(session);
	}
	
	public DetailProductDao(Connection con){
		this.connection = con;
	}
	
	public Connection getConnection(){
		return connection;
	}

	public void insertDetail(DetailProductEntity prod) throws SQLException {
		Connection connection = new ConnectionFactory().getConnection();
		try {
			
			PreparedStatement stmt = connection.prepareStatement(this.getQryInsertDetail());

			stmt.setInt(1, prod.getPosId());
			stmt.setInt(2, prod.getCategoriaId());
			stmt.setString(3, prod.getDescricao()); 
			stmt.setBoolean(4, prod.isAtivo()); 

			stmt.executeUpdate();			
			stmt.close();
			connection.close();

		} catch (SQLException e) {
			connection.rollback();
			connection.close();
			throw new RuntimeException(e);
		}
	}

	
	public boolean deleteDetail (DetailProductEntity detail) throws SQLException{
		
		Connection connection = new ConnectionFactory().getConnection();
		try {
			
			PreparedStatement stmt = connection.prepareStatement(this.qryDeletDetail());
			
			stmt.setInt(1, detail.getId());
			
			stmt.execute();
			stmt.close();
			return true;
			
		} catch (SQLException e) {
			System.out.println("SQL erro: "+ e);
			connection.close();
			return false;
		} catch (Exception e) {
			System.out.println("Geral erro: "+ e);
			connection.close();
			return false;
		}
		
	}
		

	public boolean updateDetail (DetailProductEntity detail) throws SQLException{
		
		Connection connection = new ConnectionFactory().getConnection();
		try {
			
			PreparedStatement stmt = connection.prepareStatement(this.qryUpdateDetail());

			stmt.setString(1, detail.getDescricao());
			stmt.setBoolean(2, detail.isAtivo());
			stmt.setInt(3, detail.getId());
			
			
			stmt.execute();
			stmt.close();
			connection.close();
			return true;
			
		} catch (SQLException e) {
			System.out.println("SQL erro: "+ e);
			connection.close();
			return false;
		} catch (Exception e) {
			System.out.println("Geral erro: "+ e);
			connection.close();
			return false;
		}
	}

	public  List <DetailProductEntity> getAll() throws SQLException {
		List <DetailProductEntity> relatorios = null;
		DetailProductEntity relatorio ;

		Connection connection = new ConnectionFactory().getConnection();
		try {
			
			PreparedStatement stmt = connection.prepareStatement(this.getQryDisplayAll());
			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {   
				relatorios = new ArrayList<DetailProductEntity>();
				do { 
					relatorio = new DetailProductEntity();
					
					relatorio.setId(rs.getInt("idDetalhes_Produto"));
					relatorio.setCategoriaId(rs.getInt("Categorias_idCategorias")); 
					relatorio.setDescricao(rs.getString("DescricaoProd"));
					relatorio.setDescCat(rs.getString("Descricao"));
					relatorio.setAtivo(rs.getBoolean("Ativo"));
					relatorios.add(relatorio);
				} while (rs.next());
			}
			rs.close();
			connection.close();
		} catch (SQLException e) {
			connection.close();
			System.out.println("SQL erro: "+ e);
		} catch (Exception e) {
			connection.close();
			System.out.println("Geral erro: "+ e);
		}
		return relatorios;
	}
	
	public  List <DetailProductEntity> getDetailProduct(int idProduct) throws SQLException {
		List <DetailProductEntity> prodDetails = new ArrayList<DetailProductEntity>();
		DetailProductEntity detail ;
		
		Connection connection = new ConnectionFactory().getConnection();
		try {
			
			PreparedStatement stmt = connection.prepareStatement(this.qrySelectDetailActive());
			stmt.setInt(1, idProduct);
			
			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {   				
				do { 
					detail = new DetailProductEntity();
					
					detail.setId(rs.getInt("idDetalhes_Produto"));
					detail.setCategoriaId(rs.getInt("Categorias_idCategorias")); 
					detail.setDescricao(rs.getString("DescricaoProd"));
					prodDetails.add(detail);
				} while (rs.next());
			}
			
			rs.close();
			connection.close();
		} catch (SQLException e) {
			connection.close();
			System.out.println("SQL erro: "+ e);
		} catch (Exception e) {
			connection.close();
			System.out.println("Geral erro: "+ e);
		}
		return prodDetails;
	}

	/*
	 * Query 
	 */

	private String getQryInsertDetail() {

		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO  detalhes_produto ");
		sql.append("            (POSs_idPOS, "); 
		sql.append("             Categorias_idCategorias, ");
		sql.append("             DescricaoProd, ");
		sql.append("             Ativo) ");
		sql.append("VALUES      ( ?, ?, ?, ? ); ");   

		return sql.toString();
	}


	private String getQryDisplayAll(){

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT  prod.idDetalhes_Produto, prod.Categorias_idCategorias, prod.DescricaoProd, cat.Descricao, prod.Ativo ");
		sql.append("FROM detalhes_produto as prod ");
		sql.append("	INNER JOIN categorias as cat ");
		sql.append("	ON  prod.Categorias_idCategorias = cat.idCategoria ");
		sql.append("ORDER BY cat.descricao; ");
		return sql.toString();

	}
	
	private String qryDeletDetail(){

		StringBuffer sql = new StringBuffer();
			
		sql.append("DELETE FROM detalhes_produto ");
		sql.append("WHERE       idDetalhes_Produto = ? ;");
		
		return sql.toString();
	}
	
	private String qryUpdateDetail(){

		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE  detalhes_produto ");
		sql.append("SET  	DescricaoProd = ?, ");
		sql.append("     	Ativo = ? ");
		sql.append("WHERE 	idDetalhes_Produto = ? ;");
		return sql.toString();
	}
	
	private String qrySelectDetailActive(){

		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT det.iddetalhes_produto, ");
		sql.append("       det.categorias_idcategorias, ");
		sql.append("       descricaoprod ");
		sql.append("FROM   produtos_has_categorias prod_categ ");
		sql.append("       INNER JOIN detalhes_produto det ");
		sql.append("               ON prod_categ.categorias_idcategoria = det.categorias_idcategorias ");
		sql.append("WHERE  prod_categ.produtos_idproduto = ? AND ");
		sql.append("       det.Ativo = true; ");
		
		return sql.toString();
	}
}
